﻿using ControlzEx.Standard;
using Kang.DbDoc.Interfaces;
using Kang.DbDoc.Models;
using Kang.ExtendMethod;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;

namespace Kang.DbDoc.ServicesImpl
{
    public class HtmlExportService : IExport
    {
        public bool Export(string filename, DatabaseModel database, List<TableOrViewModel> tables, List<TableOrViewModel> views, List<RoutineModel> functions, List<RoutineModel> procedures, ref string error)
        {
            try
            {
                var htmlTemplate = string.Empty;
                var title = string.Empty;
                StringBuilder contentBuilder = new StringBuilder();
                StringBuilder catalogBuilder = new StringBuilder();
                using (Stream stream = new MemoryStream(Properties.Resources.htmlTemplate))
                {
                    using (StreamReader sr = new StreamReader(stream))
                    {
                        htmlTemplate = sr.ReadToEnd();
                    }
                }
                if (database.DatabaseType == Enums.DatabaseTypeEnum.SQLite)
                {
                    title = $"{Path.GetFileName(database.DatabaseName)}表结构文档";
                }
                else
                {
                    title = $"{database.DatabaseName}表结构文档";
                }

                contentBuilder.AppendLine($"\t\t<hr>");
                contentBuilder.AppendLine($"<h3 style=\"margin: 0px 0px 0px 200px; padding: 5px 10px;\">数据库信息</h3>");
                if (database.DatabaseType == Enums.DatabaseTypeEnum.SQLite)
                {
                    contentBuilder.AppendLine($"\t\t<h4 style=\"margin: 0px 0px 0px 200px; padding: 5px 10px;\">地址：{database.DatabaseName}</h4>");
                }
                else
                {
                    contentBuilder.AppendLine($"\t\t<h4 style=\"margin: 0px 0px 0px 200px; padding: 5px 10px;\">地址：{database.DatabaseHost}</h4>");
                }
                if (database.DatabasePort > 0)
                {
                    contentBuilder.AppendLine($"\t\t<h4 style=\"margin: 0px 0px 0px 200px; padding: 5px 10px;\">端口：{database.DatabasePort}</h4>");
                }
                if (tables.IsNotBlank())
                {
                    contentBuilder.AppendLine($"\t\t<h4 style=\"margin: 0px 0px 0px 200px; padding: 5px 10px;\">表总数：{tables.Count}</h4>");
                }
                if (views.IsNotBlank())
                {
                    contentBuilder.AppendLine($"\t\t<h4 style=\"margin: 0px 0px 0px 200px; padding: 5px 10px;\">视图总数：{views.Count}</h4>");
                }
                if (functions.IsNotBlank())
                {
                    contentBuilder.AppendLine($"\t\t<h4 style=\"margin: 0px 0px 0px 200px; padding: 5px 10px;\">方法总数：{functions.Count}</h4>");
                }
                if (procedures.IsNotBlank())
                {
                    contentBuilder.AppendLine($"\t\t<h4 style=\"margin: 0px 0px 0px 200px; padding: 5px 10px;\">存储过程总数：{procedures.Count}</h4>");
                }

                if (tables.IsNotBlank())
                {
                    contentBuilder.AppendLine($"\t\t<hr>");
                    catalogBuilder.AppendLine($"\t\t\t<li style=\"list-style: none;\"><h4 style=\"margin: 0px 0px 0px -18px;\">表：</h4></li>");
                    for (int i = 0, id = 1; i < tables.Count; i++, id = 1)
                    {
                        var table = tables[i];
                        if (i > 0)
                        {
                            contentBuilder.AppendLine($"\t\t<hr>");
                        }
                        catalogBuilder.AppendLine($"\t\t\t<li><a href=\"#table_{table.Name}\">{table.Name}</a></li>");
                        contentBuilder.AppendLine($"\t\t<section id='table_{table.Name}'>");
                        contentBuilder.AppendLine($"\t\t\t<h3>表：{table.Name}</h3>");
                        contentBuilder.AppendLine($"\t\t\t<table border=\"2px solid #ddd\" style=\"border-collapse: collapse;\">");
                        contentBuilder.AppendLine($"\t\t\t\t<thead>");
                        contentBuilder.AppendLine($"\t\t\t\t\t<tr>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>序号</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>列名</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>数据类型</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>长度</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>小数</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>键类型</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>自增</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>为空</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>默认</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>列说明</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t</tr>");

                        contentBuilder.AppendLine($"\t\t\t\t</thead>");
                        contentBuilder.AppendLine($"\t\t\t\t<tbody>");
                        foreach (var col in table.Columns)
                        {
                            contentBuilder.AppendLine($"\t\t\t\t\t<tr>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{id++}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{col.Name}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{col.Type}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{col.Length}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{col.Scale}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{col.KeyType}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{(col.IsAutoIncrement ? "√" : "")}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{(col.IsNull ? "√" : "")}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{col.Default}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{col.Comment}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t</tr>");
                        }
                        contentBuilder.AppendLine($"\t\t\t\t</tbody>");
                        contentBuilder.AppendLine($"\t\t\t</table>");
                        contentBuilder.AppendLine($"\t\t</section>");
                    }
                }
                if (views.IsNotBlank())
                {
                    contentBuilder.AppendLine($"\t\t<hr>");
                    catalogBuilder.AppendLine($"\t\t\t<li style=\"list-style: none;\"><h4 style=\"margin: 0px 0px 0px -18px;\">视图：</h4></li>");
                    for (int i = 0, id = 1; i < views.Count; i++, id = 1)
                    {
                        var view = views[i];
                        if (i > 0)
                        {
                            contentBuilder.AppendLine($"\t\t<hr>");
                        }
                        catalogBuilder.AppendLine($"\t\t\t<li><a href=\"#view_{view.Name}\">{view.Name}</a></li>");
                        contentBuilder.AppendLine($"\t\t<section id='view_{view.Name}'>");
                        contentBuilder.AppendLine($"\t\t\t<h3>表：{view.Name}</h3>");
                        contentBuilder.AppendLine($"\t\t\t<table border=\"2px solid #ddd\" style=\"border-collapse: collapse;\">");
                        contentBuilder.AppendLine($"\t\t\t\t<thead>");
                        contentBuilder.AppendLine($"\t\t\t\t\t<tr>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>序号</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>列名</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>数据类型</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>长度</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>小数</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>键类型</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>自增</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>为空</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>默认</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t\t<th>列说明</th>");
                        contentBuilder.AppendLine($"\t\t\t\t\t</tr>");

                        contentBuilder.AppendLine($"\t\t\t\t</thead>");
                        contentBuilder.AppendLine($"\t\t\t\t<tbody>");
                        foreach (var col in view.Columns)
                        {
                            contentBuilder.AppendLine($"\t\t\t\t\t<tr>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{id++}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{col.Name}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{col.Type}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{col.Length}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{col.Scale}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{col.KeyType}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{(col.IsAutoIncrement ? "√" : "")}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{(col.IsNull ? "√" : "")}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{col.Default}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t\t<td>{col.Comment}</td>");
                            contentBuilder.AppendLine($"\t\t\t\t\t</tr>");
                        }
                        contentBuilder.AppendLine($"\t\t\t\t</tbody>");
                        contentBuilder.AppendLine($"\t\t\t</table>");
                        contentBuilder.AppendLine($"\t\t</section>");
                    }
                }
                if (functions.IsNotBlank())
                {
                    contentBuilder.AppendLine($"\t\t<hr>");
                    catalogBuilder.AppendLine($"\t\t\t<li style=\"list-style: none;\"><h4 style=\"margin: 0px 0px 0px -18px;\">方法：</h4></li>");
                    for (int i = 0; i < functions.Count; i++)
                    {
                        var function = functions[i];
                        if (i > 0)
                        {
                            contentBuilder.AppendLine($"\t\t<hr>");
                        }
                        catalogBuilder.AppendLine($"\t\t\t<li><a href=\"#function_{function.Name}\">{function.Name}</a></li>");
                        contentBuilder.AppendLine($"\t\t<section id='function_{function.Name}'>");
                        contentBuilder.AppendLine($"\t\t\t<h3>方法：{function.Name}</h3>");
                        contentBuilder.AppendLine($"\t\t\t<h3>创建者：{function.Definer}</h3>");
                        if (function.Comment.IsNotBlank())
                        {
                            contentBuilder.AppendLine($"<div>{function.Comment}</div>");
                        }
                        contentBuilder.AppendLine($"<pre>");
                        contentBuilder.AppendLine($"{GetSQLRenderHtmlCode(function.Definition)}</pre>");
                        contentBuilder.AppendLine($"\t\t</section>");
                    }
                }
                if (procedures.IsNotBlank())
                {
                    contentBuilder.AppendLine($"\t\t<hr>");
                    catalogBuilder.AppendLine($"\t\t\t<li style=\"list-style: none;\"><h4 style=\"margin: 0px 0px 0px -18px;\">存储过程：</h4></li>");
                    for (int i = 0; i < procedures.Count; i++)
                    {
                        var procedure = procedures[i];
                        if (i > 0)
                        {
                            contentBuilder.AppendLine($"\t\t<hr>");
                        }
                        catalogBuilder.AppendLine($"\t\t\t<li><a href=\"#function_{procedure.Name}\">{procedure.Name}</a></li>");
                        contentBuilder.AppendLine($"\t\t<section id='function_{procedure.Name}'>");
                        contentBuilder.AppendLine($"\t\t\t<h3>方法：{procedure.Name}</h3>");
                        contentBuilder.AppendLine($"\t\t\t<h3>创建者：{procedure.Definer}</h3>");
                        if (procedure.Comment.IsNotBlank())
                        {
                            contentBuilder.AppendLine($"<div>{procedure.Comment}</div>");
                        }
                        contentBuilder.AppendLine($"<pre>");
                        contentBuilder.AppendLine($"{GetSQLRenderHtmlCode(procedure.Definition)}</pre>");
                        contentBuilder.AppendLine($"\t\t</section>");
                    }
                }
                htmlTemplate = htmlTemplate.RegexReplace("{{\\.title}}", title);
                htmlTemplate = htmlTemplate.RegexReplace("{{\\.content}}", contentBuilder.ToString().TrimStart());
                htmlTemplate = htmlTemplate.RegexReplace("{{\\.catalog}}", catalogBuilder.ToString().TrimStart());
                File.WriteAllText(filename, htmlTemplate);
                return true;
            }
            catch (Exception e)
            {
                error = e.Message;
                return false;
            }
        }

        private string GetSQLRenderHtmlCode(string sql)
        {
            sql = Regex.Replace(sql, "(ORDER\\s+BY|GROUP\\s+BY|BEGIN|CREATE|PROCEDURE|DEFINER|FUNCTION|RETURNS|END|RETURN|SELECT|FROM|WHERE|ASC|DESC|AND|OR|NOT|INSERT|UPDATE|DELETE|INTO|VALUES|JOIN|ON|HAVING|LIMIT)", match => 
            {
                return $"<span class=\"sql-keyword\">{match}</span>";
            }, RegexOptions.IgnoreCase);
            sql = Regex.Replace(sql, "`([^`]+)`", match => 
            {
                return $"<span class=\"sql-identifier\">{match}</span>";
            }, RegexOptions.IgnoreCase);
            sql = Regex.Replace(sql, "'([^']+)'", match => 
            {
                return $"<span class=\"sql-string\">{match}</span>";
            }, RegexOptions.IgnoreCase);
            return sql;
        }
    }
}
